import os

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "tb3.settings")

import django
django.setup()

import pandas as pd
from django.db.models import Q
from oa.models import *
from tb3.utils import str_value, date_value, num_value, convert_choices_to_dict


def import_material_data(file_path):
    df = pd.read_excel(file_path, keep_default_na=False)
    fields = [
        'no',
        'material_category__name',
        'supplier__name',
        'name',
        'name_en',
        'code',
        'size',
        'unit',
        'desc'
    ]
    str_fields = [
        'material_category__name',
        'supplier__name',
        'name',
        'name_en',
        'code',
        'unit',
        'desc'
    ]
    total_count = 0
    created_count = 0
    updated_count = 0
    failed_count = 0
    for r in df.itertuples(index=True):
        data = {}
        for i, k in enumerate(fields):
            try:
                vl = r[i]
                if k in str_fields:
                    vl = str_value(vl)
                    if vl:
                        data[k] = vl
            except AttributeError:
                pass
        code = data['code']
        material_category__name = data.get('material_category__name')
        if material_category__name:
            data.pop('material_category__name')
        supplier__name = data.get('supplier__name')
        if supplier__name:
            data.pop('supplier__name')
        data.pop('code')
        print(code)
        print(data)
        if code:
            total_count += 1
            if material_category__name:
                material_category, mc_created = MaterialCategory.objects.get_or_create(name=material_category__name)
                data['material_category'] = material_category
                m, m_created = Material.objects.update_or_create(
                    code=code, defaults=data
                )
                if supplier__name:
                    supplier, s_created = Supplier.objects.get_or_create(name=supplier__name)
                    m.supplier.add(supplier.pk)
                if m_created:
                    created_count += 1
                else:
                    updated_count += 1


def import_material_in(file_path):
    df = pd.read_excel(file_path, keep_default_na=False)
    fields = [
        'no',
        'code',
        'order_date',
        'material_category__name',
        'material__code',
        'material__name',
        'supplier__name',
        'package_unit__unit',
        'package_unit__amount',
        'amount',
        'unit_price',
        'carriage_cost',
        'ca_price',
        'total_price',
        'total_ca_price',
        'already_pay',
        'exp_record_date',
        'real_arrived_time',
        'a1', 'a2', 'a3', 'a4'
    ]
    str_fields = [
        'material__code', 'material__name', 'supplier__name', 'package_unit__unit',
        'material_category__name'
    ]
    date_fields = ['order_date', 'exp_record_date', 'real_arrived_time']
    num_fields_4 = [
        'package_unit__amount',
        'amount',
        'unit_price',
        'carriage_cost',
        'ca_price',
        'total_price',
        'total_ca_price'
    ]
    for r in df.itertuples(index=True):
        data = {}
        for i, k in enumerate(fields):
            try:
                vl = r[i]
                if k in str_fields:
                    data[k] = str_value(vl)
                elif k in date_fields:
                    data[k] = date_value(vl)
                elif k in num_fields_4:
                    data[k] = num_value(vl, 4)
            except AttributeError:
                pass
        if data.get('material__code'):
            unit_raw = data['package_unit__unit']
            package_unit__amount = data.get('package_unit__amount', 1)
            unit = 'EA'
            if not package_unit__amount:
                package_unit__amount = 1
            if unit_raw and package_unit__amount:
                if unit_raw.lower() in ['kg', 'g']:
                    unit = unit_raw.lower()
            package_unit, created = PackageUnit.objects.get_or_create(
                display='{} {}'.format(package_unit__amount, unit_raw.lower()),
                amount=package_unit__amount,
                unit=unit,
                defaults={'is_product': False}
            )
            print(package_unit)
            material_category, created = MaterialCategory.objects.get_or_create(
                name=data['material_category__name']
            )
            material, created = Material.objects.get_or_create(
                code=data['material__code'],
                defaults={
                    'name': data['material__name'],
                    'material_category': material_category
                }
            )
            supplier, created = Supplier.objects.get_or_create(
                name=data['supplier__name']
            )
            related_purchase_order, created = MaterialPurchaseOrder.objects.get_or_create(
                supplier=supplier,
                order_date=data['order_date'],
                defaults={
                    'exp_record_date': data['exp_record_date'],
                    'state': '3',
                    'state_pay': '3'
                }
            )
            if data['total_ca_price']:
                related_purchase_order.carriage_cost = data['total_ca_price']
                related_purchase_order.save()
            amount = data['amount']
            if amount and package_unit__amount:
                packs = float(amount) / float(package_unit__amount)
            else:
                packs = 1
            related_purchase_detail, item_created = MaterialPurchaseOrderItem.objects.get_or_create(
                related_purchase_order=related_purchase_order,
                material=material,
                defaults={
                    'package_unit': package_unit,
                    'packs': packs,
                    'amount': amount,
                    'amount_in': amount,
                    'price': data['total_price']
                }
            )
            material_batches = MaterialBatch.objects.filter(
                material=material,
                package_unit=package_unit,
            ).order_by('-pk')
            if material_batches.exists():
                material_batch = material_batches.first()
            else:
                material_batch = MaterialBatch.objects.create(
                    code='unknown',
                    material=material,
                    package_unit=package_unit,
                )
            if not material_batch.package_unit == package_unit:
                print('*'*100, '\n', package_unit, '\n', '*'*100, material_batch.package_unit)
            related_order, c = MaterialInOrder.objects.get_or_create(
                related_purchase_order=related_purchase_order,
                record_date=data['order_date'],
            )
            material_in, c = MaterialIn.objects.get_or_create(
                related_order=related_order,
                material_batch=material_batch,
                related_purchase_detail=related_purchase_detail,
                # warehouse_id=2,
                defaults={'packs': packs, 'amount': amount}
            )
            print(material_in)


def import_channels(file_path):
    df = pd.read_excel(file_path, keep_default_na=False)
    for r in df.itertuples(index=True):
        customer_level, created = CustomerLevel.objects.get_or_create(level_name=r.渠道)
        customer, created = Customer.objects.update_or_create(
            nickname=r.客户, defaults={'customer_level': customer_level}
        )
        print(customer)


def auto_check_purchase_order():
    mpos = MaterialPurchaseOrder.objects.filter(Q(is_confirmed=False) | Q(state__in=['1', '2']))
    print('total count:', mpos.count())
    c = 0
    for mpo in mpos:
        it_c_all = MaterialPurchaseOrderItem.objects.filter(related_purchase_order=mpo).count()
        it_c_arr = MaterialPurchaseOrderItem.objects.filter(related_purchase_order=mpo).filter(is_arrived=True).count()
        if it_c_all:
            if it_c_all == it_c_arr:
                mpo.is_confirmed = True
                mpo.state = '3'
                mpo.save()
                c += 1
    print('updated count:', c)
    mpos = MaterialPurchaseOrder.objects.filter(Q(is_confirmed=True) | Q(state__in=['3']))
    print('total count:', mpos.count())
    c = 0
    for mpo in mpos:
        it_c_all = MaterialPurchaseOrderItem.objects.filter(related_purchase_order=mpo).count()
        it_c_arr = MaterialPurchaseOrderItem.objects.filter(related_purchase_order=mpo).filter(is_arrived=True).count()
        if it_c_all:
            if not it_c_all == it_c_arr:
                mpo.is_confirmed = False
                mpo.state = '1'
                mpo.save()
                c += 1
    print('updated count:', c)


def get_public_materials(url='http://127.0.0.1:8080/api/public_materials/'):
    import requests
    import json
    data = requests.get(url)
    total_count = 0
    updated_count = 0
    created_count = 0
    material_category_dict = dict()
    while data.status_code == 200:
        text = json.loads(data.text)
        results = text['results']
        for material in results:
            if material['material_category']['name'] not in material_category_dict:
                material_category = material['material_category']
                material_category.pop('id')
                mc, created = MaterialCategory.objects.get_or_create(
                    name=material_category['name'],
                    defaults=material_category
                )
                material_category_id = mc.pk
                material_category_dict[material['material_category']['name']] = material_category_id
            else:
                material_category_id = material_category_dict[material['material_category']['name']]
            material['material_category_id'] = material_category_id
            material.pop('datetime_created')
            material.pop('datetime_updated')
            material.pop('material_category')
            material.pop('id')
            m, created = Material.objects.update_or_create(code=material['code'], defaults=material)
            if created:
                created_count += 1
            else:
                updated_count += 1
            total_count += 1
        if text['next']:
            data = requests.get(text['next'])
        else:
            break
    return created_count, updated_count


def read_money_io_file(file_path='media/003.xlsx'):
    import xlrd
    from approval.models import ApplicationType, OppositeAccount, MoneyFlow, direction_choices
    book = xlrd.open_workbook(file_path)
    df = pd.read_excel(file_path, keep_default_na=False)
    opposite_account_dict = dict()
    direction_choices_dict = convert_choices_to_dict(direction_choices, rv=True)
    for sheet in book.sheets():
        print(sheet.name)
        if not sheet.name == '5601 销售费用':
            sheet_name = sheet.name
            application_type_code = sheet_name.split(' ')[0]
            application_type_name = sheet_name.split(' ')[1]
            if len(application_type_code) == 6:
                application_type, created = ApplicationType.objects.update_or_create(
                    code=application_type_code,
                    defaults={'name': application_type_name}
                )
                application_type_id = application_type.pk
                df = pd.read_excel(file_path, sheet.name, header=6, keep_default_na=False)
                for r in df.itertuples(index=True):
                    if getattr(r, '方向') in ['借', '贷']:
                        if not getattr(r, '借方') == '' and getattr(r, '贷方') == '':
                            opposite_account = getattr(r, '对方科目')
                            if opposite_account not in opposite_account_dict:
                                opposite_account_obj, created = OppositeAccount.objects.get_or_create(
                                    name=opposite_account
                                )
                                opposite_account_dict[opposite_account] = opposite_account_obj.pk
                                opposite_account_id = opposite_account_obj.pk
                            else:
                                opposite_account_id = opposite_account_dict[opposite_account]
                            direction = direction_choices_dict[getattr(r, '方向')]
                            data = {
                                'application_type': sheet_name,
                                'related_application_type_id': application_type_id,
                                'record_date': getattr(r, '日期'),
                                'record_code': getattr(r, '凭证字号'),
                                'record_notes': getattr(r, '摘要'),
                                'opposite_account': getattr(r, '对方科目'),
                                'related_opposite_account_id': opposite_account_id,
                                'debit_side': getattr(r, '借方'),
                                'credit_side': 0,
                                'direction': direction,
                            }
                            mf, created = MoneyFlow.objects.get_or_create(**data)
                            print(mf)
    return df


def generate_default_price(create=0):
    package_groups = PackageGroup.objects.all()
    sales_channels = SalesChannel.objects.all()
    for package_group in package_groups:
        if package_group.code:
            package_group_code = str(package_group.code)
            if len(package_group_code) == 4:
                if package_group_code[0] in ['B', 'D']:
                    if package_group_code[1] in ['1', '2', '3', '4']:
                        for sales_channel in sales_channels:
                            if sales_channel.sales_channel_type in [1, 2, 4]:
                                print(package_group, sales_channel)
                                if create:
                                    ProductSalesPrice.objects.get_or_create(
                                        package_group=package_group,
                                        sales_channel=sales_channel,
                                        defaults={'price': 0}
                                    )
                elif package_group_code[0] in ['P', 'K']:
                    for sales_channel in sales_channels:
                        if sales_channel.sales_channel_type in [3, 4]:
                            print(package_group, sales_channel)
                            if create:
                                ProductSalesPrice.objects.get_or_create(
                                    package_group=package_group,
                                    sales_channel=sales_channel,
                                    defaults={'price': 0}
                                )
            elif len(package_group_code) == 8 and package_group.total_amount == 24:
                for sales_channel in sales_channels:
                    if sales_channel.sales_channel_type in [1, 2, 3, 4]:
                        print(package_group, sales_channel)
                        if create:
                            ProductSalesPrice.objects.get_or_create(
                                package_group=package_group,
                                sales_channel=sales_channel,
                                defaults={'price': 0}
                            )
            elif len(package_group_code) == 10:
                for sales_channel in sales_channels:
                    if sales_channel.sales_channel_type in [2, 4]:
                        print(package_group, sales_channel)
                        if create:
                            ProductSalesPrice.objects.get_or_create(
                                package_group=package_group,
                                sales_channel=sales_channel,
                                defaults={'price': 0}
                            )


if __name__ == '__main__':
    # import_material_data('media/material20200509.xlsx')
    # compare_name('media/material_inventory20200503.xlsx')
    # data = calculate_cost_of_product(1)
    # import_material_in('media/material_in.xlsx')
    # import_channels('media/channels.xlsx')
    # auto_check_purchase_order()
    get_public_materials('http://tr.techbrew.cn/api/public_materials/')
    # get_kingdee_inventory()
